import os
import json
import logging
from db_config import execute_query

# 设置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def read_pitch_file(pitch_file_path):
    """读取pitch文件中的频率数据"""
    try:
        with open(pitch_file_path, 'r', encoding='utf-8') as f:
            lines = f.readlines()
            # 去除空行和换行符
            frequencies = [float(line.strip()) for line in lines if line.strip()]
            return frequencies
    except Exception as e:
        logger.error(f"读取pitch文件失败: {e}")
        return []

def create_frequency_data(frequencies):
    """创建JSON格式的频率数据"""
    data = []
    # 假设每个频率点的时间间隔为0.01秒
    time_interval = 0.01
    
    for i, freq in enumerate(frequencies):
        time_point = round(i * time_interval, 2)  # 保留两位小数
        data.append({"time": time_point, "frequency": freq})
    
    return json.dumps({"data": data})

def import_standard_audio(character_text, pinyin, tone_type, audio_file, pitch_file):
    """导入标准音频数据到数据库"""
    try:
        # 验证文件是否存在
        if not os.path.exists(audio_file):
            logger.error(f"音频文件不存在: {audio_file}")
            return False
            
        if not os.path.exists(pitch_file):
            logger.error(f"Pitch文件不存在: {pitch_file}")
            return False
        
        # 读取pitch数据
        frequencies = read_pitch_file(pitch_file)
        if not frequencies:
            logger.error(f"Pitch文件为空: {pitch_file}")
            return False
        
        # 创建频率数据JSON
        frequency_data = create_frequency_data(frequencies)
        
        # 保存相对路径
        audio_rel_path = os.path.relpath(audio_file, os.path.dirname(os.path.abspath(__file__)))
        audio_rel_path = audio_rel_path.replace('\\', '/')  # 统一使用正斜杠
        
        # 插入数据库
        sql = """
        INSERT INTO standard_audios (character_text, pinyin, tone_type, audio_path, frequency_data)
        VALUES (%s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE
        audio_path = VALUES(audio_path),
        frequency_data = VALUES(frequency_data)
        """
        
        execute_query(sql, (character_text, pinyin, tone_type, audio_rel_path, frequency_data))
        logger.info(f"成功导入标准音频: {character_text} ({pinyin})")
        return True
        
    except Exception as e:
        logger.error(f"导入标准音频失败: {e}")
        return False

def batch_import_from_table(data_file):
    """从表格数据文件批量导入"""
    success_count = 0
    error_count = 0
    
    try:
        with open(data_file, 'r', encoding='utf-8') as f:
            lines = f.readlines()
            # 跳过表头
            for line in lines[1:]:
                fields = line.strip().split('\t')
                if len(fields) >= 9:
                    try:
                        # 根据你提供的表格结构解析数据
                        id_num = fields[0]
                        character = fields[1]
                        pinyin = fields[2]
                        pinyin_number = fields[6]  # 使用拼音2字段
                        
                        # 从拼音确定声调类型
                        tone_type = get_tone_type(pinyin_number)
                        
                        # 构建文件路径 (使用文件名字段)
                        file_name = fields[8].strip()
                        audio_file = os.path.join('static', 'audio', f"{file_name}.wav")
                        pitch_file = os.path.join('static', 'audio', f"{file_name}.pitch")
                        
                        # 确保目录存在
                        os.makedirs(os.path.join('static', 'audio'), exist_ok=True)
                        
                        # 导入数据
                        if import_standard_audio(character, pinyin_number, tone_type, audio_file, pitch_file):
                            success_count += 1
                        else:
                            error_count += 1
                    except Exception as e:
                        logger.error(f"处理行失败: {line.strip()}, 错误: {e}")
                        error_count += 1
        
        logger.info(f"批量导入完成，成功: {success_count}, 失败: {error_count}")
        return True
    
    except Exception as e:
        logger.error(f"批量导入失败: {e}")
        return False

def get_tone_type(pinyin):
    """从拼音中提取声调类型"""
    # 处理像"a1"这样的拼音格式
    for char in pinyin:
        if char.isdigit():
            return int(char)
    return 0  # 默认为轻声

def create_pitch_file_from_data(file_path, data_string):
    """从字符串数据创建pitch文件"""
    try:
        os.makedirs(os.path.dirname(file_path), exist_ok=True)
        with open(file_path, 'w', encoding='utf-8') as f:
            f.write(data_string)
        logger.info(f"成功创建pitch文件: {file_path}")
        return True
    except Exception as e:
        logger.error(f"创建pitch文件失败: {e}")
        return False

def import_single_audio():
    """手动导入单个音频示例"""
    character = "阿"
    pinyin = "a1"
    tone_type = 1
    # 使用示例文件名格式
    audio_file = "static/audio/1_0001_1_a1_0_a1.wav"
    pitch_file = "static/audio/1_0001_1_a1_0_a1.pitch"
    
    # 确保目录存在
    os.makedirs(os.path.dirname(audio_file), exist_ok=True)
    
    # 检查pitch文件是否存在，如果不存在则创建一个示例
    if not os.path.exists(pitch_file):
        sample_data = """164.0
161.3
160.9
161.4
162.7
164.3
165.4
166.9
167.7
169.3
170.4
170.0
169.8
171.6
172.5
172.8
173.6
174.0
172.8
170.4
167.9
166.0
164.4
162.8
161.2
160.6
160.9
162.1
165.3
173.3
174.2
172.2"""
        create_pitch_file_from_data(pitch_file, sample_data)
    
    # 导入数据
    import_standard_audio(character, pinyin, tone_type, audio_file, pitch_file)

if __name__ == "__main__":
    # 确保音频目录存在
    os.makedirs(os.path.join('static', 'audio'), exist_ok=True)
    
    # 用户选择
    print("请选择导入方式:")
    print("1. 导入单个示例音频（阿, a1）")
    print("2. 从表格批量导入")
    choice = input("请输入选择 (1 或 2): ").strip()
    
    if choice == '1':
        import_single_audio()
    elif choice == '2':
        data_file = "audio_data.tsv"
        if os.path.exists(data_file):
            batch_import_from_table(data_file)
        else:
            logger.error(f"数据文件不存在: {data_file}")
            print("请提供包含音频信息的数据文件。")
    else:
        print("无效的选择。") 